package dbLayer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import modelLayer.Address;

public class DbAddress {
	private Connection con;

	public DbAddress(){
		con = DbConnection.getInstance().getDBcon();
	}

	/**
	 * Retrieves a list of all the address's from
	 * the database for the booking.
	 * @param int bookingId
	 * @return ArrayList<Address> list
	 */
	public ArrayList<Address> retrieveForBooking(int bookingId) {
		ArrayList<Address> address = new ArrayList<Address>();
		address.add(singleWhere(" address.addressId = bookingaddress.addressId AND bookingaddress.bookingId =" + bookingId + " AND bookingaddress.addressType = 0", true));
		address.add(singleWhere(" address.addressId = bookingaddress.addressId AND bookingaddress.bookingId =" + bookingId + " AND bookingaddress.addressType = 1", true));
		return address;
	}
	
	
	
	/**
	 * Retrieves an address by it's location name.
	 * @param locationName
	 * @return
	 */
	public Address retrieveByLocation(String locationName) {
		return singleWhere(" locationName = '" + locationName + "'", true);
	}
	
	/**
	 * Retrieves a address from database by
	 * finding address by id.
	 * @param int addressId
	 * @return Address
	 */
	public Address retrieveById(int addressId) {
		return singleWhere(" addressId = " + addressId, true);
	}
	/**
	 * Retrieves a list of all address's from
	 * the database for the customer.
	 * @param int partnerId
	 * @return ArrayList<Address> list
	 */
	public ArrayList<Address> retrieveAdListForCustomer(int partnerId) {
		return multiWhere(" partnerId =" + partnerId, true);
	}
	/**
	 * Retrieve address from database for
	 * the supplier
	 * @param int partnerId
	 * @return Address
	 */
	public Address retrieveForSupplier(int partnerId) {
		return singleWhere(" partnerId =" + partnerId, true);
	}
	/**
	 * Insert a address object into the database.
	 * @param Address aObj
	 * @return int (rc)
	 * @throws Exception
	 */
	public int insert(Address aObj) throws Exception {
		int ra = -1;

		String query = "Insert into address (addressId, locationName, street, houseNo, postalCode, sequenceNo)"
				+ "Values(?,?,?,?,?,?)";
		System.out.println(query);
		try{
			PreparedStatement prpstmt = con.prepareStatement(query);
			prpstmt.setInt(1, aObj.getAddressId());
			prpstmt.setString(2, aObj.getLocationName());
			prpstmt.setString(3, aObj.getStreet());
			prpstmt.setString(4, aObj.getHouseNo());
			prpstmt.setString(5, aObj.getPostalCode());
			prpstmt.setInt(6, aObj.getSequenceNo());
			prpstmt.setQueryTimeout(5);
			ra = prpstmt.executeUpdate();
			prpstmt.close();
		} catch(Exception e) {
			throw new Exception("Address not inserted correct");
		}
		return ra;
	}
	/**
	 * Insert booking address into the
	 * database.
	 * @param Address address
	 * @param int bookingId
	 * @return int (ra)
	 * @throws Exception
	 */
	public int insertBookingAddress(Address address, int addressType, int bookingId) throws Exception {

		int ra = -1;

		String query = "Insert into BookingAddress(addressId, addressType, bookingId)"
				+ "Values(?,?,?)";
		System.out.println(query);
		try{
			PreparedStatement prpstmt = con.prepareStatement(query);
			prpstmt.setInt(1, address.getAddressId());
			prpstmt.setInt(2, addressType);
			prpstmt.setInt(3, bookingId);
			prpstmt.setQueryTimeout(5);
			ra = prpstmt.executeUpdate();
			prpstmt.close();
		} catch(Exception e) {
			e.printStackTrace();
			throw new Exception("AddressBooking not inserted correct");
		}
		return ra;
	}

	/**
	 * Update a address in the database.
	 * @param Address address
	 * @return int (ra)
	 */
	public int update(Address address) {
		int ra = -1;

		String query = "Update Address SET " +
				"locationName ='" + address.getLocationName()
				+ "', street ='" + address.getStreet()
				+ "', houseNo ='" + address.getHouseNo()
				+ "', postalCode ='" + address.getPostalCode() + "'"
				+ ", sequenceNo =" + address.getSequenceNo() 
				+ " Where addressId =" + address.getAddressId();
		System.out.println(query);

		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			ra = stmt.executeUpdate(query);
			stmt.close();
		} catch(Exception e) {
			System.out.println(e);
		}
		return ra;
	}
	/**
	 * Update a businesspartner address
	 * in the database.
	 * @param Address address
	 * @param int partnerId
	 * @return int (ra)
	 */
	public int updateBpAddress(Address address, int partnerId) {

		int ra = -1;

		String query = "Update Address SET partnerId =" + partnerId +
				", locationName ='" + address.getLocationName()
				+ "', street ='" + address.getStreet()
				+ "', houseNo ='" + address.getHouseNo()
				+ "', postalCode ='" + address.getPostalCode() + "'"
				+ ", sequenceNo =" + address.getSequenceNo()
				+ " Where addressId =" + address.getAddressId();

		try{
			System.out.println(query);
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			ra = stmt.executeUpdate(query);
			stmt.close();
		} catch(Exception e) {
			System.out.println(e);
		}
		return ra;
	}
	/**
	 * Delete a address in 
	 * the database.
	 * @param Address address
	 * @return int (ra)
	 */
	public int delete(Address address) {
		int ra = -1;

		String query = "Delete from address Where addressId =" + address.getAddressId();
		System.out.println(query);//Test

		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			ra = stmt.executeUpdate(query);
			stmt.close();
		} catch(Exception e) {}
		return ra;
	}
	/**
	 * Creates a query statement.
	 * Executes query.
	 * Builds a single address object with
	 * or without associations.
	 * @param String wClause
	 * @param boolean retrieveAssociation
	 * @return Address object
	 */
	private Address singleWhere(String wClause, boolean retrieveAssociation) {
		ResultSet results;
		Address aObj = new Address();

		String query = buildQuery(wClause);

		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if(results.next()) {
				aObj = buildAddress(results);
				stmt.close();
			}
		} catch(Exception e) {}
		return aObj;
	}
	/**
	 * Creates a query.
	 * Executes the query.
	 * Builds a ArrayList of all
	 * the Address objects from the
	 * database and returns the list.
	 * @param StringwClause
	 * @param boolean retrieveAssociation
	 * @return ArrayList<Address> list
	 */
	private ArrayList<Address> multiWhere(String wClause, boolean retrieveAssociation){
		ResultSet results;
		ArrayList<Address> aList = new ArrayList<Address>();

		String query = buildQuery(wClause);
		//System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while(results.next()) {
				aList.add(buildAddress(results));
			}
			stmt.close();
		} catch(Exception e) {}
		return aList;
	}
	/**
	 * Builds a full SQL query
	 * ready for getting executed.
	 * @param String wClause
	 * @return String query
	 */
	private String buildQuery(String wClause){
		String query = "SELECT addressId, locationName, street, houseNo, address.postalCode, city, country, sequenceNo from address, PostalCode";

		if(wClause.length() > 0 && !wClause.contains(" address.addressId = bookingaddress.addressId AND bookingaddress.bookingId =")) {
			query = query + " WHERE " + wClause + " AND address.postalCode = PostalCode.postalCode";
		} else if(wClause.contains(" address.addressId = bookingaddress.addressId AND bookingaddress.bookingId =")){
			query = "SELECT address.addressId, locationName, street, houseNo, address.postalCode, city, country, sequenceNo from address, PostalCode, BookingAddress" + " Where address.postalCode = Postalcode.postalCode AND" + wClause;
		}
		return query;
	}
	/**
	 * Builds a address object
	 * from the results of the
	 * executed query.
	 * @param ResultSet results
	 * @return Address object
	 */
	private Address buildAddress(ResultSet results) {
		Address aObj = new Address();

		try {
			aObj.setAddressId(results.getInt("addressId"));
			aObj.setLocationName(results.getString("locationName"));
			aObj.setStreet(results.getString("street"));
			aObj.setHouseNo(results.getString("houseNo"));
			aObj.setPostalCode(results.getString("postalCode"));
			aObj.setCity(results.getString("city"));
			aObj.setCountry(results.getString("country"));
			aObj.setSequenceNo(results.getInt("sequenceNo"));
		}catch(Exception e) {}
		return aObj;
	}

	/**
	 * Method for retrieving a list of addresses based on their postalcode. Also not directly associated with the rest of the dbclass.
	 * @param text
	 * @return
	 */
	public ArrayList<String> retrievePCListByCode(String text) {
		ArrayList<String> matches = new ArrayList<String>();
		ResultSet results;
		String query = "Select postalcode from postalcode where postalCode.postalCode like '%" + text + "%'";
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while(results.next()) {
				matches.add(results.getString("postalcode"));
			}
			stmt.close();
			
		} catch(Exception e) {
			e.printStackTrace();
		}
		
		return matches;
		//return multiWhere(" address.postalCode like '%" + text + "%'", true);
	}
	
	/**
	 * A little method created with the purpose of retrieving a country based on the postalcode. Does not utilize the correct db methods.
	 * @param postalCode
	 * @return
	 */
	public String retrieveCountryByCode(String postalCode) {
		String country = null;
		String query = "Select postalCode, city, country from postalCode Where PostalCode.postalCode ='" + postalCode + "'";
		ResultSet results;
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if(results.next()) {
				country = results.getString("country");
				stmt.close();
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return country;
	}

	public String retrieveCityByCode(String string) {
		String city = null;
		String query = "Select city from postalCode Where postalCode.postalCode ='" + string + "'";
		ResultSet results;
		
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if(results.next()) {
				city = results.getString("city");
				stmt.close();
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return city;
	}

	

	
}
